package com.xjrsoft.module.customerTwo.barin;

import cn.smallbun.screw.core.util.CollectionUtils;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.xjrsoft.core.tool.utils.JdbcUtils;
import com.xjrsoft.module.customerTwo.barin.t_barin_srerp_list.entity.TBarinSrerpList;
import com.xjrsoft.module.customerTwo.barin.t_barin_srerp_list.service.ITBarinSrerpListService;
import com.xjrsoft.module.customerTwo.barin.t_barin_srerp_report.entity.TBarinSrerpReport;
import com.xjrsoft.module.customerTwo.barin.t_barin_srerp_report.service.ITBarinSrerpReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Configuration      //1.主要用于标记配置类，兼备Component的效果。
@EnableScheduling   // 2.开启定时任务
public class ScheduledTask {
    @Autowired
    private ITBarinSrerpListService tBarinSrerpListService;
    @Autowired
    private ITBarinSrerpReportService itBarinSrerpReportService;

    private static final String IP = "125.124.34.158";
    private static final String PORT = "3306";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "Ycloud147258@";

    /**
     * 定时每天凌晨一点执行，获取企业数据报表
     */
    @Scheduled(cron = "0 0 1 ? * *")
    private void addSrerpReportData(){
        //获取分享版开通企业
        Wrapper<TBarinSrerpList> wrapper = Wrappers.<TBarinSrerpList>query().lambda().eq(TBarinSrerpList::getFStatus,"1");
        List<TBarinSrerpList> srerpList = tBarinSrerpListService.list(wrapper);
        if(CollectionUtils.isNotEmpty(srerpList)){
            String sql = "SELECT sum(bomNumber) bomNumber,sum(seOrderNum) seOrderNum,sum(ppOrderNum) ppOrderNum,sum(icmoNum) icmoNum,sum(icStockBillNum) icStockBillNum FROM (" +
                    "select COUNT(1) AS bomNumber,0 seOrderNum,0 ppOrderNum,0 icmoNum,0 icStockBillNum FROM T_ICItem " +
                    "UNION  " +
                    "select 0 AS bomNumber,COUNT(1) seOrderNum,0 ppOrderNum,0 icmoNum,0 icStockBillNum FROM SeOrder " +
                    "UNION  " +
                    "select 0 AS bomNumber,0 seOrderNum,COUNT(1) ppOrderNum,0 icmoNum,0 icStockBillNum FROM PPOrder " +
                    "UNION " +
                    "select 0 AS bomNumber,0 seOrderNum,0 ppOrderNum,COUNT(1) icmoNum,0 icStockBillNum FROM ICMO " +
                    "UNION " +
                    "select 0 AS bomNumber,0 seOrderNum,0 ppOrderNum,0 icmoNum,COUNT(1) icStockBillNum FROM ICStockBill " +
                    ") a";
            List<TBarinSrerpReport> result = new ArrayList<>();
            for(TBarinSrerpList one : srerpList){
                if(StringUtils.isBlank(one.getFDatabasename())){
                    log.warn("ScheduledTask==============> no databasename,firename[{}]account[{}]",one.getFFirmname(),one.getFAccount());
                    continue;
                }
                String url = "jdbc:mysql://"+IP+":"+PORT+"/"+one.getFDatabasename();
                try {
                    //连接数据库
                    Connection conn = JdbcUtils.getConnect(url,USERNAME,PASSWORD);
                    Statement stmtMySQL = conn.createStatement();
                    ResultSet rsMySQL = stmtMySQL.executeQuery("SELECT * FROM users");
                    while (rsMySQL.next()) {
                        TBarinSrerpReport tBarinSrerpReport = new TBarinSrerpReport();
                        tBarinSrerpReport.setFFirmname(one.getFFirmname());
                        tBarinSrerpReport.setFBomnumber(rsMySQL.getInt("bomNumber"));
                        tBarinSrerpReport.setFSeordernum(rsMySQL.getInt("seOrderNum"));
                        tBarinSrerpReport.setFPpordernum(rsMySQL.getInt("ppOrderNum"));
                        tBarinSrerpReport.setFIcmonum(rsMySQL.getInt("icmoNum"));
                        tBarinSrerpReport.setFIcstockbillnum(rsMySQL.getInt("icStockBillNum"));
                        result.add(tBarinSrerpReport);
                    }
                }catch (Exception e){
                    log.warn("ScheduledTask==============> query data error,firename[{}]account[{}]",one.getFFirmname(),one.getFAccount());
                }
            }
            if(CollectionUtils.isNotEmpty(result)){
                //插入数据前先删除原表数据
                itBarinSrerpReportService.remove(new QueryWrapper<>());
                itBarinSrerpReportService.saveBatch(result);
            }
        }else {
            log.info("ScheduledTask==============> no data");
        }
    }
}
